﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using vnFingerPrint.BusinessObject;
using vnCommon;
namespace vnFingerPrint.DataAccess
{
    public static class VanTay_DataAccess
    {
        //Select VanTay
        public static List<VanTay_Object> SelectAll()
        {
            string SelectQuery = @"Select * from [VanTay]";
            List<VanTay_Object> result = new List<VanTay_Object>();
            DataTable tb = commonDatabase.GetSQLDataTable(clsConnectionString.GetConnectionString(), SelectQuery);
            foreach (DataRow row in tb.Rows)
            {
                VanTay_Object newObject = new VanTay_Object();
                newObject.So_CMND = (string)row["So_CMND"];
                if (row["DacTrung"].ToString() != "") newObject.DacTrung = (string)row["DacTrung"];
                if (row["ImgUrl"].ToString() != "") newObject.ImgUrl = (string)row["ImgUrl"];
                newObject.LoaiNgon = (string)row["LoaiNgon"];
                result.Add(newObject);
            }
            return result;
        }
        public static VanTay_Object GetObject(int _id)
        {
            string SelectQuery = @"Select * from [VanTay] where [Id]= @Id";
            List<SqlParameter> paralist = new List<SqlParameter>();
            paralist.Add(new SqlParameter("Id", _id));
            DataTable tb = commonDatabase.GetSQLDataTable(clsConnectionString.GetConnectionString(), SelectQuery, paralist);
            DataRow row = tb.Rows[0];
            VanTay_Object newObject = new VanTay_Object();
            newObject.So_CMND = (string)row["So_CMND"];
            if (row["DacTrung"].ToString() != "") newObject.DacTrung = (string)row["DacTrung"];
            if (row["ImgUrl"].ToString() != "") newObject.ImgUrl = (string)row["ImgUrl"];
            newObject.LoaiNgon = (string)row["LoaiNgon"]; newObject.Id = long.Parse(row["Id"].ToString());
            return newObject;
        }
        public static List<VanTay_Object> GetObject(string _So_CMND)
        {
            string SelectQuery = @"Select * from [VanTay] where [So_CMND]= @So_CMND";
            List<SqlParameter> paralist = new List<SqlParameter>();
            paralist.Add(new SqlParameter("So_CMND", _So_CMND));
            List<VanTay_Object> result = new List<VanTay_Object>();
            DataTable tb = commonDatabase.GetSQLDataTable(clsConnectionString.GetConnectionString(), SelectQuery, paralist);
            foreach (DataRow row in tb.Rows)
            {
                VanTay_Object newObject = new VanTay_Object();
                newObject.So_CMND = (string)row["So_CMND"];
                if (row["DacTrung"].ToString() != "") newObject.DacTrung = (string)row["DacTrung"];
                if (row["ImgUrl"].ToString() != "") newObject.ImgUrl = (string)row["ImgUrl"];
                newObject.LoaiNgon = (string)row["LoaiNgon"];
                result.Add(newObject);
            }
            return result;
        }
        //Insert VanTay
        public static int Insert(VanTay_Object newObject)
        {
            string InsertQuery = "Insert Into [VanTay]";
            List<SqlParameter> paralist = new List<SqlParameter>();
            string _val = "", _para = "";
            _val += "So_CMND, "; _para += "@So_CMND, ";
            paralist.Add(new SqlParameter("So_CMND", newObject.So_CMND));
            if (newObject.DacTrung != null)
            {
                _val += "DacTrung, "; _para += "@DacTrung, ";
                paralist.Add(new SqlParameter("DacTrung", newObject.DacTrung));
            }
            if (newObject.ImgUrl != null)
            {
                _val += "ImgUrl, "; _para += "@ImgUrl, ";
                paralist.Add(new SqlParameter("ImgUrl", newObject.ImgUrl));
            }
            _val += "LoaiNgon, "; _para += "@LoaiNgon, ";
            paralist.Add(new SqlParameter("LoaiNgon", newObject.LoaiNgon));
            if (newObject.CoreDelta != null)
            {
                _val += "CoreDelta"; _para += "@CoreDelta";
                paralist.Add(new SqlParameter("CoreDelta", newObject.CoreDelta));
            }


            InsertQuery += "(" + _val + ") Values (" + _para + ")";
            InsertQuery = InsertQuery.Replace(", )", ")");
            return commonDatabase.ExecuteNonQuerySQL(clsConnectionString.GetConnectionString(), InsertQuery, paralist);
        }
        //Get an Object of VanTay
        public static VanTay_Object GetObject(string _So_CMND, string _LoaiNgon)
        {
            List<SqlParameter> paralist = new List<SqlParameter>();
            VanTay_Object newObject = new VanTay_Object();
            string SelectQuery = "Select * from [VanTay] where (1=1) and [So_CMND]= @So_CMND and [LoaiNgon]= @LoaiNgon";
            paralist.Add(new SqlParameter("So_CMND", _So_CMND));
            paralist.Add(new SqlParameter("LoaiNgon", _LoaiNgon));
            DataTable tb = commonDatabase.GetSQLDataTable(clsConnectionString.GetConnectionString(), SelectQuery, paralist);
            if (tb.Rows.Count < 1) return null;
            DataRow row = tb.Rows[0];
            newObject.So_CMND = (string)row["So_CMND"];
            if (row["DacTrung"].ToString() != "") newObject.DacTrung = (string)row["DacTrung"];
            if (row["ImgUrl"].ToString() != "") newObject.ImgUrl = (string)row["ImgUrl"];
            newObject.LoaiNgon = (string)row["LoaiNgon"];
            return newObject;
        }
        //Save changes of an Object to database VanTay
        public static int SaveChanged(VanTay_Object UpdatingObject)
        {
            List<SqlParameter> paralist = new List<SqlParameter>();
            string UpdateQuery =
            @"Update VanTay SET
               DacTrung= @DacTrung,
               ImgUrl= @ImgUrl,
               CoreDelta=@CoreDelta 
            Where So_CMND= @So_CMND and LoaiNgon= @LoaiNgon";
            paralist.Add(new SqlParameter("So_CMND", UpdatingObject.So_CMND));
            paralist.Add(new SqlParameter("DacTrung", UpdatingObject.DacTrung));
            paralist.Add(new SqlParameter("ImgUrl", UpdatingObject.ImgUrl));
            paralist.Add(new SqlParameter("LoaiNgon", UpdatingObject.LoaiNgon));
            paralist.Add(new SqlParameter("CoreDelta", UpdatingObject.CoreDelta));
            return commonDatabase.ExecuteNonQuerySQL(clsConnectionString.GetConnectionString(), UpdateQuery, paralist);
        }
        //Delete an Object in database VanTay
        public static int Delete(VanTay_Object DeletingObject)
        {
            List<SqlParameter> paralist = new List<SqlParameter>();
            string DeleteQuery =
            @"Delete [VanTay]
            Where So_CMND= @So_CMND and LoaiNgon= @LoaiNgon";
            paralist.Add(new SqlParameter("So_CMND", DeletingObject.So_CMND));
            paralist.Add(new SqlParameter("LoaiNgon", DeletingObject.LoaiNgon));
            return commonDatabase.ExecuteNonQuerySQL(clsConnectionString.GetConnectionString(), DeleteQuery, paralist);
        }

        public static void Submit(VanTay_Object VanTayObject)
        {
            if (GetObject(VanTayObject.So_CMND, VanTayObject.LoaiNgon) == null)
                Insert(VanTayObject);
            else SaveChanged(VanTayObject);
        }

    }
}